package DAO;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;



import model.Book;
import model.Page;


public class BookDAO {
	//数据库连接字符串，注意数据库的名称(20150032)和字符编码(UTF-8)
	private String JDBCURL = "jdbc:mysql://localhost:3306/2015124049?useUnicode=true&characterEncoding=UTF-8";
	//数据库访问账号
	private String user = "root"; 
	//账号密码
	private String password = "123456"; 
	//数据库连接对象
	private Connection conn = null;
	private Statement stmt = null;
	//数据库操作申明
	//数据库访问结果集
	private ResultSet rst = null;
	private PreparedStatement preStmt=null;
	/**
	 * 获取所有图书信息
	 * @return
	 */
	public List<Book> getBookByPage(Page page){
		List<Book> books = new ArrayList<Book>();
		try {
			//加载MySQL数据库驱动程序
			Class.forName("com.mysql.jdbc.Driver");
			//创建数据库连接
			conn = DriverManager.getConnection(JDBCURL,user,password);
			//定义数据库操作SQL语句:查询t_book表所有记录和所有字段
			String sql = "SELECT * FROM t_book limit ?,?";
			//创建数据库操作申明
			//执行数据库查询，返回结果集
			preStmt=conn.prepareStatement(sql);
			preStmt.setInt(1,page.getStartPos());
			preStmt.setInt(2,page.getPageSize());
			rst = preStmt.executeQuery();
			Book book = null;
			//对结果集进行遍历，
			while(rst.next()){
				//将每条数据封装成一个新的Book对象，一定要new
				book = new Book();
				book.setUuid(rst.getInt("uuid"));
				book.setBookName(rst.getString("bookName"));
				book.setPicUrl(rst.getString("picUrl"));
				book.setPrice(rst.getFloat("price"));
				//将Book对象保存到List集合中
				books.add(book);
			}	
		} catch (Exception e) {
			e.printStackTrace();
		}finally{  //一定要使用finally，释放资源
			try {
				preStmt.close();  //关闭申明
				conn.close();  //关闭连接
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		//返回结果集
		return books;		
	}
	public int getTotalCount(){
		int totalCount=0;
		try {
			//加载MySQL数据库驱动程序
			Class.forName("com.mysql.jdbc.Driver");
			//创建数据库连接
			conn = DriverManager.getConnection(JDBCURL,user,password);
			//定义数据库操作SQL语句:查询t_book表所有记录和所有字段
			String sql = "SELECT count(*) total FROM t_book";
			//创建数据库操作申明
			//执行数据库查询，返回结果集
			stmt=conn.createStatement();
			rst = stmt.executeQuery(sql);
			while(rst.next()) {
				totalCount=rst.getInt("total");
			}
				
		} catch (Exception e) {
			e.printStackTrace();
		}finally{  //一定要使用finally，释放资源
			try {
				stmt.close();  //关闭申明
				conn.close();  //关闭连接
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	
		return totalCount;		
	}
	public int getDoubleNameCount(String bookName){
		int totalCount=0;
		try {
			//加载MySQL数据库驱动程序
			Class.forName("com.mysql.jdbc.Driver");
			//创建数据库连接
			conn = DriverManager.getConnection(JDBCURL,user,password);
			//定义数据库操作SQL语句:查询t_book表所有记录和所有字段
			String sql = "SELECT count(*) total FROM t_book where bookname=?";
			preStmt=conn.prepareStatement(sql);
			preStmt.setString(1, bookName);
			rst =preStmt.executeQuery();
			while(rst.next()) {
				totalCount=rst.getInt("total");
			}
				
		} catch (Exception e) {
			e.printStackTrace();
		}finally{  //一定要使用finally，释放资源
			try {
				preStmt.close();  //关闭申明
				conn.close();  //关闭连接
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	
		return totalCount;		
	}
	/**
	 * 根据id获取某条Book对象
	 * @param id
	 * @return
	 */
	public Book getById(int id){
		Book book = null;
		try {
			//加载MySQL数据库驱动程序
			Class.forName("com.mysql.jdbc.Driver");
			//创建数据库连接
			conn = DriverManager.getConnection(JDBCURL,user,password);
			//定义数据库查询SQL语句:根据id查询t_book表某条记录的所有字段		
			String sql = "SELECT * FROM t_book WHERE ";
			sql += " id=" + id;
			System.out.println(sql);
			//创建数据库操作申明
			stmt = conn.createStatement();
			//执行数据库查询，返回结果集		
			rst = stmt.executeQuery(sql);
			while(rst.next()){
				book = new Book();
				book.setUuid(rst.getInt("id"));
				book.setBookName(rst.getString("bookname"));
				book.setPicUrl(rst.getString("pic_url"));
				book.setPrice(rst.getFloat("price"));
			}	
		} catch (Exception e) { 
			//程序运行异常处理：显示出错的堆栈信息
			e.printStackTrace();
		}finally{  //一定要使用finally，释放资源
			try {
				stmt.close();  //关闭申明
				conn.close();  //关闭连接
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return book;		
	}
	
	/**
	 * 添加Book记录
	 * @param book
	 * @return
	 */
	public int add(Book book){
		int result = -1;
		try {
			//加载MySQL数据库驱动程序
			Class.forName("com.mysql.jdbc.Driver");
			//创建数据库连接
			conn = DriverManager.getConnection(JDBCURL,user,password);
			//定义数据库新增语句
			String sql = "INSERT INTO t_book(bookName,picUrl,price) VALUES(";
			sql += "'" + book.getBookName();
			sql += "','" + book.getPicUrl();
			sql += "'," + book.getPrice() + ")";
			System.out.println(sql);
			//创建数据库操作申明
			stmt = conn.createStatement();
			//执行数据库更新操作，返回影响行数
			result = stmt.executeUpdate(sql);	
		} catch (Exception e) {
			e.printStackTrace();
		}finally{  //一定要使用finally，释放资源
			try {
				stmt.close();  //关闭申明
				conn.close();  //关闭连接
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;		
	}

	/**
	 * 根据id更新Book信息
	 * @param book
	 * @return
	 */
	public int update(Book book){
		int result = -1;
		try {
			//加载MySQL数据库驱动程序
			Class.forName("com.mysql.jdbc.Driver");
			//创建数据库连接
			conn = DriverManager.getConnection(JDBCURL,user,password);
			//定义数据库更新语句		
			String sql = "UPDATE t_book SET";
			sql += " bookname='" + book.getBookName();
			sql += "',pic_url='" + book.getPicUrl();
			sql += "',price=" + book.getPrice() + " WHERE ";
			sql +=	" id="	+ book.getUuid();
			System.out.println(sql);
			//创建数据库操作申明
			stmt = conn.createStatement();
			//执行数据库更新操作，返回影响行数		
			result = stmt.executeUpdate(sql);	
		} catch (Exception e) {
			e.printStackTrace();
		}finally{  //一定要使用finally，释放资源
			try {
				stmt.close();  //关闭申明
				conn.close();  //关闭连接
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;		
	}

	/**
	 * 根据id删除Book对象
	 * @param bookid
	 * @return
	 */
	public int delete(int bookid){
		int result = -1;
		try {
			//加载MySQL数据库驱动程序
			Class.forName("com.mysql.jdbc.Driver");
			//创建数据库连接
			conn = DriverManager.getConnection(JDBCURL,user,password);
			//定义数据库删除语句			
			String sql = "delete from t_book WHERE";
			sql +=	" id="	+ bookid;
			System.out.println(sql);
			//创建数据库操作申明
			stmt = conn.createStatement();
			//执行数据库更新操作，返回影响行数		
			result = stmt.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{  //一定要使用finally，释放资源
			try {
				stmt.close();  //关闭申明
				conn.close();  //关闭连接
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;		
	}
	
	/**
	 * BookDAO方法测试
	 * @param args
	 */
	public static void main(String[] args) {
		BookDAO bookDao = new BookDAO();	
		//删除测试
		//System.out.println("delete:" + bookDao.delete(1));
		//查询测试
		//Book book = bookDao.getById(2);
		//if(book != null){
			//System.out.println("book:" + book.toString());
		//}
		int totalCount=bookDao.getTotalCount();
		Page page=new Page(1,totalCount);
        System.out.println(bookDao.getBookByPage(page));
	}
}
